using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Charts;
using DevExpress.XtraSpreadsheet.Commands;
using DocumentFormat.OpenXml.Bibliography;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace Shopfloormanagement
{
    public partial class CostSavingLoader : DevExpress.XtraEditors.XtraForm
    {
        // 类级别变量，用于跟踪工作表修改状态
        private Dictionary<string, bool> modifiedSheets = new Dictionary<string, bool>();
        public static bool isCalculate = false; //判定是新增项目还是编辑项目
        public static int calculateID = 0;
        public static bool isOverview = false;
        public static bool isOverviewwithSheets = false;

        private bool isDirty = false;
        public CostSavingLoader()
        {
            InitializeComponent();
            spreadsheetFormulaBar1.SpreadsheetControl = spreadsheetCIPList;
            InitializeCustomComponents();
            xtraTabControl1.ShowTabHeader = DevExpress.Utils.DefaultBoolean.False;

        }

        private void InitializeCustomComponents()
        {

            this.WindowState = FormWindowState.Maximized;

            // 添加文档加载完成事件
            spreadsheetCIPList.DocumentLoaded += SpreadsheetCIPList_DocumentLoaded;
            // 添加内容变更事件
            spreadsheetCIPList.ContentChanged += SpreadsheetCIPList_ContentChanged;

            // Load the Excel file into the spreadsheet control
            spreadsheetCIPList.LoadDocument(Properties.Resources.CIP_template);




            if (isOverview)
            {
                // Load projects
                LoadProjects();

                //Create pivot table
                CreatePivotTable();
                CreatDataSummary();

                //CreateChart();

            }
            else


            {
                LoadOneProjects();


            }
            //智能检测保存用
            spreadsheetCIPList.CellValueChanged += (s, e) => isDirty = true;

        }

        private void SpreadsheetCIPList_DocumentLoaded(object sender, EventArgs e)
        {
            // 初始化修改状态字典
            modifiedSheets.Clear();
            foreach (Worksheet sheet in spreadsheetCIPList.Document.Worksheets)
            {
                modifiedSheets[sheet.Name] = false;
            }
        }

        private void SpreadsheetCIPList_ContentChanged(object sender, EventArgs e)
        {
            try
            {
                // 获取当前活动工作表
                Worksheet activeSheet = spreadsheetCIPList.ActiveWorksheet;
                if (activeSheet == null) return;

                // 获取当前选中的单元格
                CellRange selection = spreadsheetCIPList.Selection;
                if (selection == null) return;

                // 获取选中的单元格
                Cell cell = activeSheet.Cells[selection.TopRowIndex, selection.LeftColumnIndex];

                // 检查是否是公式
                if (!string.IsNullOrEmpty(cell.Formula))
                {
                    ProcessCellFormula(cell);
                }

                // 标记工作表为已修改
                if (activeSheet.Index > 1 && modifiedSheets.ContainsKey(activeSheet.Name))
                {
                    modifiedSheets[activeSheet.Name] = true;
                }
            }
            catch (Exception ex)
            {
                // 错误处理，但不中断用户操作
                Console.WriteLine($"处理公式时出错: {ex.Message}");
            }
        }

        private void ProcessCellFormula(Cell cell)
        {
            if (cell == null || string.IsNullOrEmpty(cell.Formula))
                return;

            // 检查是否已经包含IFERROR
            if (!cell.Formula.StartsWith("=IFERROR(") && !cell.Formula.Contains(",IFERROR("))
            {
                // 包装公式
                string originalFormula = cell.Formula;
                // 如果公式不是以=开头，添加=
                if (!originalFormula.StartsWith("="))
                {
                    originalFormula = "=" + originalFormula;
                }

                // 去掉开头的=，因为在IFERROR函数内部不需要
                string formulaContent = originalFormula.StartsWith("=") ?
                    originalFormula.Substring(1) : originalFormula;

                // 用IFERROR包装
                string safeFormula = $"=IFERROR({formulaContent},0)";

                // 应用新公式
                cell.Formula = safeFormula;

                // 立即计算此单元格
                Worksheet sheet = cell.Worksheet;
                if (sheet != null)
                {
                    sheet.Calculate();
                }
            }
        }

        private void LoadProjects()
        {
            // TODO: Load projects from database
            // 创建一个 DateTime 变量，值为去年 1 月 1 日
            DateTime currentDate = CIPSFMView.selectdate;
            int lastYear = currentDate.Year - 1;
            int thisYear = lastYear + 1;
            int nextYear = lastYear + 2;

            DateTime lastYearJanuaryFirst = new DateTime(lastYear, 1, 1);
            //查询逻辑：C.上一年+今年所有项目
            string condition = $"delTag=0 and createDate >='{lastYearJanuaryFirst}' order by PID";
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", condition);

            // the 3nd worksheet is the Overview
            int idx = spreadsheetCIPList.Document.Worksheets.Count;
            Console.WriteLine(idx);
            Worksheet overviewSheet = spreadsheetCIPList.Document.Worksheets["Overview"];

            // Map and load data from DataTable to the spreadsheet
            //动态载入表头日期
            overviewSheet.Cells[2, 9].Value = lastYearJanuaryFirst.Year;
            overviewSheet.Cells[2, 10].Value = lastYearJanuaryFirst.Year + 1;
            overviewSheet.Cells[2, 11].Value = lastYearJanuaryFirst.Year + 2;
            overviewSheet.Cells[2, 49].Value = lastYearJanuaryFirst.Year;
            overviewSheet.Cells[2, 50].Value = lastYearJanuaryFirst.Year + 1;
            overviewSheet.Cells[2, 51].Value = lastYearJanuaryFirst.Year + 2;

            overviewSheet.Cells[2, 13].Value = lastYearJanuaryFirst.ToString("yyyy/MM");
            for (int a = 0; a < 36; a++)
            {
                overviewSheet.Cells[2, 13 + a].Value = lastYearJanuaryFirst.AddMonths(a).ToString("yyyy/MM");
                overviewSheet.Cells[2, 53 + a].Value = lastYearJanuaryFirst.AddMonths(a).ToString("yyyy/MM");
            }
            //查询是哪个工厂的数据库，为循环时写入对应工厂的部门清单用
            string connectionString = SystemInfo.SQL;

            // 从连接字符串中提取数据库名称,透视表和Overview都需要
            string databaseName = "";
            string[] connectionParts = connectionString.Split(';');
            foreach (string part in connectionParts)
            {
                if (part.Trim().StartsWith("Database=", StringComparison.OrdinalIgnoreCase))
                {
                    databaseName = part.Trim().Substring(9);
                    break;
                }
            }




            //载入基本信息
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];

                // Map DataTable columns to Overview columns
                overviewSheet.Cells[i + 3, 0].Value = row["PID"].ToString(); // Maps to Column1
                overviewSheet.Cells[i + 3, 1].Value = row["topic"].ToString(); // Maps to Column2
                overviewSheet.Cells[i + 3, 2].Value = row["type"].ToString(); // Maps to Column3
                overviewSheet.Cells[i + 3, 3].Value = row["owner"].ToString(); // Maps to Column3
                //overviewSheet.Cells[i + 3, 4].Value = row["subDepartment"].ToString(); // Maps to Column3
                overviewSheet.Cells[i + 3, 5].Value = row["DepartmentName"].ToString(); // Maps to Column3
                overviewSheet.Cells[i + 3, 6].Value = row["Initiator"].ToString(); // Maps to Column3
                overviewSheet.Cells[i + 3, 7].Value = row["IL"].ToString(); // Maps to Column3




                ////DeptOrder 透视表排序用
                //if (databaseName == "SFM_MECYPiston")
                //{
                //    switch (row["DepartmentName"].ToString())
                //    {
                //        case "Foundry":
                //            overviewSheet.Cells[i + 3, 5].Value = "01Foundry";
                //            break;
                //        case "Machining":
                //            overviewSheet.Cells[i + 3, 5].Value = "02Machining";
                //            break;
                //        case "FI/ST/PA":
                //            overviewSheet.Cells[i + 3, 5].Value = "03FI/ST/PA";
                //            break;
                //        case "M/c Building":
                //            overviewSheet.Cells[i + 3, 5].Value = "04M/c Building";
                //            break;
                //        case "Logistics":
                //            overviewSheet.Cells[i + 3, 5].Value = "05Logistics";
                //            break;
                //        case "Quality":
                //            overviewSheet.Cells[i + 3, 5].Value = "06Quality";
                //            break;
                //        case "PS/PT":
                //            overviewSheet.Cells[i + 3, 5].Value = "07PS/PT";
                //            break;
                //        default:
                //            overviewSheet.Cells[i + 3, 5].Value = "08Others"; // 其他部门排最后
                //            break;
                //    }

                //}
                //else if (databaseName == "SFM_MECYBearing")
                //{
                //    switch (row["DepartmentName"].ToString())
                //    {
                //        case "Production_Maching":
                //            overviewSheet.Cells[i + 3, 5].Value = "01Production_Maching";
                //            break;
                //        case "Production_Polymer":
                //            overviewSheet.Cells[i + 3, 5].Value = "02Production_Polymer";
                //            break;
                //        case "Production_Bushing":
                //            overviewSheet.Cells[i + 3, 5].Value = "03Production_Bushing";
                //            break;
                //        case "PT":
                //            overviewSheet.Cells[i + 3, 5].Value = "04PT";
                //            break;
                //        case "Quality":
                //            overviewSheet.Cells[i + 3, 5].Value = "05Quality";
                //            break;
                //        case "Logistics":
                //            overviewSheet.Cells[i + 3, 5].Value = "06Logistics";
                //            break;
                //        case "Maintenance":
                //            overviewSheet.Cells[i + 3, 5].Value = "07Maintenance";
                //            break;
                //        case "Tooling":
                //            overviewSheet.Cells[i + 3, 5].Value = "08Tooling";
                //            break;

                //        default:
                //            overviewSheet.Cells[i + 3, 5].Value = "09Others"; // 其他部门排最后
                //            break;
                //    }

                //}
                //else if (databaseName == "SFM_MECYCamshaft")
                //{
                //    switch (row["DepartmentName"].ToString())
                //    {
                //        case "一区":
                //            overviewSheet.Cells[i + 3, 5].Value = "01一区";
                //            break;
                //        case "二区":
                //            overviewSheet.Cells[i + 3, 5].Value = "02二区";
                //            break;
                //        case "PT":
                //            overviewSheet.Cells[i + 3, 5].Value = "03PT";
                //            break;
                //        case "Quality":
                //            overviewSheet.Cells[i + 3, 5].Value = "04Quality";
                //            break;
                //        case "Maintenance":
                //            overviewSheet.Cells[i + 3, 5].Value = "05Maintenance";
                //            break;
                //        case "Logistics":
                //            overviewSheet.Cells[i + 3, 5].Value = "06Logistics";
                //            break;
                //        case "Tooling":
                //            overviewSheet.Cells[i + 3, 5].Value = "07Tooling";
                //            break;
                //        default:
                //            overviewSheet.Cells[i + 3, 5].Value = "08Others"; // 其他部门排最后
                //            break;
                //    }

                //}
                //else if (databaseName == "SFM_MECYRing")
                //{
                //    switch (row["DepartmentName"].ToString())
                //    {
                //        case "Cast iron ring":
                //            overviewSheet.Cells[i + 3, 5].Value = "01Cast iron ring";
                //            break;
                //        case "Steel ring":
                //            overviewSheet.Cells[i + 3, 5].Value = "02Steel ring";
                //            break;
                //        case "3-Pieces ring":
                //            overviewSheet.Cells[i + 3, 5].Value = "033-Pieces ring";
                //            break;
                //        case "PT":
                //            overviewSheet.Cells[i + 3, 5].Value = "04PT";
                //            break;
                //        case "Quality":
                //            overviewSheet.Cells[i + 3, 5].Value = "05Quality";
                //            break;
                //        case "Maintenance":
                //            overviewSheet.Cells[i + 3, 5].Value = "06Maintenance";
                //            break;
                //        case "Logistics":
                //            overviewSheet.Cells[i + 3, 5].Value = "07Logistics";
                //            break;
                //        case "Tooling":
                //            overviewSheet.Cells[i + 3, 5].Value = "08Tooling";
                //            break;

                //        default:
                //            overviewSheet.Cells[i + 3, 5].Value = "09Others"; // 其他部门排最后
                //            break;
                //    }

                //}





                if (row["IL6CurrentDueDate"] != DBNull.Value)
                {
                    overviewSheet.Cells[i + 3, 8].Value = Convert.ToDateTime(row["IL6CurrentDueDate"]).ToString("yyyy/MM/dd");
                }
                else
                {
                    overviewSheet.Cells[i + 3, 8].Value = null;
                }
                //measureID
                if (row["measureID"] != DBNull.Value)
                {
                    overviewSheet.Cells[i + 3, 89].Value = row["measureID"].ToString();
                }
                else
                {
                    overviewSheet.Cells[i + 3, 89].Value = null;
                }



                //载入当前项目saving信息
                DataTable dt1 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and [CIPID]={row["ID"]} order by ID");

                //根据saving对应的年份动态存储budget
                if (dt1 != null && dt1.Rows.Count > 0)
                {
                    // 处理第0行数据
                    if (dt1.Rows.Count > 0)
                    {
                        for (int r = 0; r <= 1; r++)
                        {
                            //planned
                            for (int j = 2; j <= 13; j++)
                            {
                                if (j < dt1.Columns.Count && dt1.Rows[r][j] != DBNull.Value)
                                {
                                    int year = Convert.ToInt32(dt1.Rows[r][1]);

                                    if (year == lastYear)
                                    {
                                        // 先保留1位小数，再赋值，这样汇总分析时，也是1位小数
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 11].Value = value;
                                    }
                                    else if (year == thisYear)
                                    {
                                        // 先保留1位小数，再赋值
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 23].Value = value;
                                    }
                                    else if (year == nextYear)
                                    {
                                        // 先保留1位小数，再赋值
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 35].Value = value;
                                    }
                                }
                            }
                            //actual
                            for (int j = 2; j <= 13; j++)
                            {
                                if (j < dt1.Columns.Count && dt1.Rows[r + 2][j] != DBNull.Value)
                                {
                                    int year = Convert.ToInt32(dt1.Rows[r + 2][1]);

                                    if (year == lastYear)
                                    {
                                        // 先保留1位小数，再赋值
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r + 2][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 51].Value = value;
                                    }
                                    else if (year == thisYear)
                                    {
                                        // 先保留1位小数，再赋值
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r + 2][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 63].Value = value;
                                    }
                                    else if (year == nextYear)
                                    {
                                        // 先保留1位小数，再赋值
                                        decimal value = Math.Round(Convert.ToDecimal(dt1.Rows[r + 2][j]), 1);
                                        overviewSheet.Cells[i + 3, j + 75].Value = value;
                                    }
                                }
                            }
                        }
                    }
                }
                if (isOverviewwithSheets)
                {
                    // Create a new worksheet for each project
                    int projectPID = Convert.ToInt32(row["PID"]);
                    string sheetName = projectPID.ToString();
                    LoadSheetData_Byte(Convert.ToInt32(row["PID"]));

                    //// Create a copy of the Cost saving template旧的单元格式的加载代码
                    //Worksheet newSheet = spreadsheetCIPList.Document.Worksheets.Add(sheetName);
                    //newSheet.CopyFrom(spreadsheetCIPList.Document.Worksheets[0]);

                    ////加载该项目的工作表数据
                    //LoadSheetData(newSheet, Convert.ToInt32(row["ID"]));


                    ////存储方式后不需要了
                    ////动态加载月份,以项目saving year第一年起
                    //int firstSavingYear = Convert.ToInt32(dt1.Rows[0][1]);
                    //DateTime FirstSavingYear = new DateTime(firstSavingYear, 1, 1);

                    //for (int a = 0; a < 24; a++)
                    //{
                    //    newSheet.Cells[4, 7 + a].Value = FirstSavingYear.AddMonths(a).ToString("yyyy/MM");
                    //}

                    ////加载Topic
                    //newSheet.Cells[3, 6].Value = row["topic"].ToString();

                    // 添加到修改状态字典
                    modifiedSheets[sheetName] = false;


                }

            }
            //Overview进入设置当前激活sheet
            spreadsheetCIPList.Document.Worksheets.ActiveWorksheet = spreadsheetCIPList.Document.Worksheets["Overview"];
            //隐藏模版
            spreadsheetCIPList.Document.Worksheets[0].Visible = false;

        }
        private void LoadOneProjects()
        {
            // 获取当前选中项目的PID
            int projectPID = CIPAdd.SelectPID;
            string sheetName = projectPID.ToString();

            // 先通过PID获取ID
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"PID={projectPID} and delTag=0");
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show($"Project with PID {projectPID} not found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            int projectId = Convert.ToInt32(dt.Rows[0]["ID"]);

            if (dt.Rows[0]["SheetData"] == DBNull.Value)
            {
                //初次加载
                // Create a copy of the Cost saving template//不需要了
                Worksheet newSheet = spreadsheetCIPList.Document.Worksheets.Add(sheetName);
                newSheet.CopyFrom(spreadsheetCIPList.Document.Worksheets[0]);
                //载入当前项目saving信息
                DataTable dt1 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and [CIPID]={projectId} order by ID");

                if (dt1.Rows.Count > 0)
                {
                    int firstSavingYear = Convert.ToInt32(dt1.Rows[0][1]);
                    DateTime FirstSavingYear = new DateTime(firstSavingYear, 1, 1);

                    for (int a = 0; a < 24; a++)
                    {
                        newSheet.Cells[4, 7 + a].Value = FirstSavingYear.AddMonths(a).ToString("yyyy/MM");
                    }
                }

                //加载Topic
                newSheet.Cells[3, 6].Value = dt.Rows[0]["topic"].ToString();


            }
            else
            {
                //不是初次加载，已有数据，载入byte即可
                LoadSheetData_Byte(projectId);
            }




            //隐藏其余的sheet
            spreadsheetCIPList.Document.Worksheets["Cost saving template "].Visible = false;
            spreadsheetCIPList.Document.Worksheets["Summary"].Visible = false;
            spreadsheetCIPList.Document.Worksheets["Overview"].Visible = false;
            spreadsheetCIPList.Document.Worksheets["Pivot table"].Visible = false;


            // 添加到修改状态字典
            modifiedSheets[sheetName] = false;
        }


        private void btnSave_Click(object sender, EventArgs e)
        {
            isDirty = false;
            // 检查是否有修改过的工作表
            bool hasModifiedSheets = false;
            foreach (var kvp in modifiedSheets)
            {
                if (kvp.Value)
                {
                    hasModifiedSheets = true;
                    break;
                }
            }

            if (!hasModifiedSheets)
            {
                MessageBox.Show("No modified worksheets detected, no need to save.",
                    "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            // 保存之前询问用户
            DialogResult result = MessageBox.Show(
                "Confirm saving modified project worksheet data?",
                "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (result == DialogResult.Yes)
            {
                // 显示进度指示
                Cursor = Cursors.WaitCursor;

                try
                {
                    // 保存已修改的工作表数据
                    SaveModifiedSheetData();
                }
                finally
                {
                    // 恢复光标
                    Cursor = Cursors.Default;
                }
            }
        }

        // 处理所有单元格公式
        private void ProcessAllFormulasInSheet(Worksheet sheet)
        {
            if (sheet == null) return;

            // 获取使用范围
            CellRange usedRange = sheet.GetUsedRange();
            if (usedRange == null) return;

            int rowCount = usedRange.RowCount;
            int colCount = usedRange.ColumnCount;

            // 遍历所有单元格
            for (int row = 0; row < rowCount; row++)
            {
                for (int col = 0; col < colCount; col++)
                {
                    Cell cell = sheet.Cells[row, col];

                    // 处理公式
                    ProcessCellFormula(cell);
                }
            }

            // 计算整个工作表
            sheet.Calculate();
        }

        // 添加一个方法来处理可能错误的单元格值（用于UI按钮）
        public void ProcessCurrentSheetFormulas()
        {
            //判断是项目公式页才执行
            if (spreadsheetCIPList.ActiveWorksheet != null && spreadsheetCIPList.ActiveWorksheet != spreadsheetCIPList.Document.Worksheets[0] && spreadsheetCIPList.ActiveWorksheet != spreadsheetCIPList.Document.Worksheets["Summary"] && spreadsheetCIPList.ActiveWorksheet != spreadsheetCIPList.Document.Worksheets["Overview"])
            {
                Cursor = Cursors.WaitCursor;
                try
                {
                    ProcessAllFormulasInSheet(spreadsheetCIPList.ActiveWorksheet);
                    MessageBox.Show("All formulas have been processed, error values have been converted to 0.", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    Cursor = Cursors.Default;
                }
            }
        }

        /// <summary>
        /// 保存已修改的项目工作表数据
        /// </summary>
        private void SaveModifiedSheetData()
        {
            int totalModifiedSheets = 0;
            int successCount = 0;

            try
            {
                using (SqlConnection connection = new SqlConnection(SystemInfo.SQL))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            // 遍历所有工作表
                            foreach (Worksheet sheet in spreadsheetCIPList.Document.Worksheets)
                            {
                                string sheetName = sheet.Name;

                                // 跳过模板工作表(索引0)和概览工作表(索引1)和summary
                                if (sheetName == "Template" || sheetName == "Summary" || sheetName == "Overview" || sheetName == "Pivot table")
                                    continue;

                                int projectPID;
                                // 只处理以数字命名的项目工作表
                                if (!int.TryParse(sheetName, out projectPID))
                                    continue;

                                // 只处理已修改的工作表
                                if (!modifiedSheets.ContainsKey(sheetName) || !modifiedSheets[sheetName])
                                    continue;

                                totalModifiedSheets++;

                                // 先通过PID获取ID
                                DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"PID={projectPID} and delTag=0");
                                if (dt.Rows.Count == 0)
                                {
                                    MessageBox.Show($"Project with PID {projectPID} not found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    continue;
                                }

                                int projectId = Convert.ToInt32(dt.Rows[0]["ID"]);

                                //// 物理删除该项目的现有数据
                                //using (SqlCommand deleteCmd = new SqlCommand(
                                //    "DELETE FROM CIP_SheetData WHERE CIPID=@projectId",
                                //    connection, transaction))
                                //{
                                //    deleteCmd.Parameters.AddWithValue("@projectId", projectId);
                                //    deleteCmd.ExecuteNonQuery();
                                //}

                                //// 获取工作表的使用范围
                                //CellRange usedRange = sheet.GetUsedRange();
                                //if (usedRange == null) continue;

                                //int rowCount = usedRange.RowCount;
                                //int colCount = usedRange.ColumnCount;

                                // 创建一个24个值的decimal数组来存储项目saving结果
                                decimal[] savingResults = new decimal[24];

                                // 提取Cells[16, 7]至Cells[16, 30]的值
                                for (int col = 7; col <= 30; col++)
                                {
                                    // 确保单元格的值可以转换为decimal
                                    // 使用?.ToString() ?? ""简化空值检查
                                    string cellValueStr = sheet.Cells[16, col].Value?.ToString() ?? "";
                                    if (!string.IsNullOrEmpty(cellValueStr.Trim()) && decimal.TryParse(cellValueStr, out decimal cellValue))
                                    {
                                        savingResults[col - 7] = cellValue;
                                    }
                                    else
                                    {
                                        // 如果无法转换，设置为0
                                        savingResults[col - 7] = 0m;
                                    }
                                }

                                DataTable dt1 = LSQL.SQL.ReturnDataTableAsc(SystemInfo.SQL, "CIP_saving", $"delTag=0 and [CIPID]={projectId}");
                                int firstSavingYear = Convert.ToInt32(dt1.Rows[0][1]);

                                //第一年
                                string updateContent = $"[1]={savingResults[0]},[2]={savingResults[1]},[3]={savingResults[2]},[4]={savingResults[3]},[5]={savingResults[4]},[6]={savingResults[5]},[7]={savingResults[6]},[8]={savingResults[7]},[9]={savingResults[8]},[10]={savingResults[9]},[11]={savingResults[10]},[12]={savingResults[11]}";
                                string condition = $"delTag=0 and year={firstSavingYear} and [CIPID]={projectId} and [savingType] ='Actual'";
                                bool success = LSQL.SQL.Update(SystemInfo.SQL, "CIP_saving", updateContent, condition);

                                //第二年
                                string updateContent2 = $"[1]={savingResults[12]},[2]={savingResults[13]},[3]={savingResults[14]},[4]={savingResults[15]},[5]={savingResults[16]},[6]={savingResults[17]},[7]={savingResults[18]},[8]={savingResults[19]},[9]={savingResults[20]},[10]={savingResults[21]},[11]={savingResults[22]},[12]={savingResults[23]}";
                                string condition2 = $"delTag=0 and year={firstSavingYear + 1} and [CIPID]={projectId} and [savingType] ='Actual'";
                                bool success2 = LSQL.SQL.Update(SystemInfo.SQL, "CIP_saving", updateContent2, condition2);

                                //Total actual Saving
                                decimal totalSaving = 0m;
                                // 使用?.ToString() ?? ""简化空值检查
                                string cellValueStr1 = sheet.Cells[16, 6].Value?.ToString() ?? "";
                                if (!string.IsNullOrEmpty(cellValueStr1.Trim()) && decimal.TryParse(cellValueStr1, out decimal cellValue1))
                                {
                                    totalSaving = cellValue1;
                                }

                                string updateContent3 = $"actualSaving={totalSaving}";
                                string condition3 = $"[ID]={projectId} and delTag=0";
                                bool success3 = LSQL.SQL.Update(SystemInfo.SQL, "CIP_list", updateContent3, condition3);

                                //// 批量插入命令
                                //using (SqlCommand insertCmd = new SqlCommand(
                                //    "INSERT INTO CIP_SheetData (CIPID, SheetName, CellRow, CellColumn, CellValue, IsFormula) " +
                                //    "VALUES (@CIPID, @SheetName, @CellRow, @CellColumn, @CellValue, @IsFormula)",
                                //    connection, transaction))
                                //{
                                //    // 创建参数
                                //    insertCmd.Parameters.Add("@CIPID", SqlDbType.Int);
                                //    insertCmd.Parameters.Add("@SheetName", SqlDbType.NVarChar, 50);
                                //    insertCmd.Parameters.Add("@CellRow", SqlDbType.Int);
                                //    insertCmd.Parameters.Add("@CellColumn", SqlDbType.Int);
                                //    insertCmd.Parameters.Add("@CellValue", SqlDbType.NVarChar, -1);
                                //    insertCmd.Parameters.Add("@IsFormula", SqlDbType.Bit);

                                //    // 遍历单元格并保存
                                //    for (int row = 0; row < rowCount; row++)
                                //    {
                                //        for (int col = 0; col < colCount; col++)
                                //        {
                                //            Cell cell = sheet.Cells[row, col];

                                //            // 跳过空单元格
                                //            if (cell.Value == null && string.IsNullOrEmpty(cell.Formula))
                                //                continue;

                                //            bool isFormula = !string.IsNullOrEmpty(cell.Formula);
                                //            string cellValue = isFormula ? cell.Formula : cell.Value?.ToString() ?? string.Empty;

                                //            // 设置参数值
                                //            insertCmd.Parameters["@CIPID"].Value = projectId;
                                //            insertCmd.Parameters["@SheetName"].Value = sheetName;
                                //            insertCmd.Parameters["@CellRow"].Value = row;
                                //            insertCmd.Parameters["@CellColumn"].Value = col;
                                //            insertCmd.Parameters["@CellValue"].Value = cellValue;
                                //            insertCmd.Parameters["@IsFormula"].Value = isFormula;

                                //            // 执行插入
                                //            insertCmd.ExecuteNonQuery();
                                //        }
                                //    }
                                //}

                                // 新增：将Sheet整体序列化为byte[]并存入CIP_list.SheetData
                                try
                                {
                                    using (var tempSpreadsheet = new DevExpress.XtraSpreadsheet.SpreadsheetControl())
                                    {
                                        // 获取源工作表
                                        Worksheet sourceSheet = spreadsheetCIPList.Document.Worksheets[sheetName];

                                        // 直接添加具有目标名称的工作表（此时工作簿为空，名称必唯一）
                                        Worksheet tempSheet = tempSpreadsheet.Document.Worksheets.Add(sheetName);


                                        // 删除簿生成时，自带的那个sheet
                                        while (tempSpreadsheet.Document.Worksheets.Count > 1)
                                        {
                                            Worksheet worksheetToDelete = tempSpreadsheet.Document.Worksheets[0];
                                            tempSpreadsheet.Document.Worksheets.Remove(worksheetToDelete);
                                        }


                                        // 复制源工作表的所有内容到临时工作表
                                        tempSheet.CopyFrom(sourceSheet);


                                        // 重命名为目标名称
                                        tempSheet.Name = sheet.Name;

                                        // 将临时工作簿保存到字节数组
                                        using (MemoryStream ms = new MemoryStream())
                                        {
                                            tempSpreadsheet.Document.SaveDocument(ms, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                                            byte[] sheetBytes = ms.ToArray();

                                            // 更新数据库
                                            using (SqlCommand updateSheetCmd = new SqlCommand(
                                                "UPDATE CIP_list SET SheetData = @SheetData WHERE PID = @PID", connection, transaction))
                                            {
                                                updateSheetCmd.Parameters.Add("@SheetData", SqlDbType.VarBinary, -1).Value = sheetBytes;
                                                updateSheetCmd.Parameters.Add("@PID", SqlDbType.Int).Value = projectPID;
                                                updateSheetCmd.ExecuteNonQuery();
                                            }
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    // 记录但不中断主流程
                                    Console.WriteLine($"保存SheetData失败: {ex.Message}");
                                }

                                // 保存成功后，将工作表标记为未修改
                                modifiedSheets[sheetName] = false;
                                successCount++;
                            }

                            // 提交事务
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            // 发生错误时回滚事务
                            transaction.Rollback();
                            throw new Exception("保存过程中发生错误: " + ex.Message);
                        }
                    }
                }

                if (totalModifiedSheets > 0)
                {
                    MessageBox.Show($"Save successful! Saved {successCount}/{totalModifiedSheets} modified project worksheets.",
                        "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("No modified worksheets detected, no need to save.",
                        "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Save failed: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /// <summary>
        /// 加载项目工作表数据
        /// </summary>
        private void LoadSheetData(Worksheet sheet, int projectId)
        {
            try
            {
                // 查询该项目的所有单元格数据
                string condition = $"CIPID={projectId} and delTag =0";
                DataTable dt = LSQL.SQL.ReturnDataTableAsc(SystemInfo.SQL, "CIP_SheetData", condition);

                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int cellRow = Convert.ToInt32(row["CellRow"]);
                        int cellCol = Convert.ToInt32(row["CellColumn"]);
                        string cellValue = row["CellValue"].ToString();
                        bool isFormula = Convert.ToBoolean(row["IsFormula"]);

                        try
                        {
                            // 设置单元格值或公式
                            if (isFormula)
                            {
                                sheet.Cells[cellRow, cellCol].Formula = cellValue;
                            }
                            else
                            {
                                // 尝试根据单元格内容格式化值
                                if (decimal.TryParse(cellValue, out decimal numValue))
                                {
                                    sheet.Cells[cellRow, cellCol].Value = numValue;
                                }
                                else if (DateTime.TryParse(cellValue, out DateTime dateValue))
                                {
                                    sheet.Cells[cellRow, cellCol].Value = dateValue;
                                }
                                else
                                {
                                    sheet.Cells[cellRow, cellCol].Value = cellValue;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            // 记录错误但继续处理下一个单元格
                            Console.WriteLine($"Error setting cell [{cellRow},{cellCol}]: {ex.Message}");
                        }
                    }

                    // 在加载完数据后，处理所有公式
                    ProcessAllFormulasInSheet(sheet);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Failed to load project {projectId} data: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void simpleButtonFormula_Click(object sender, EventArgs e)
        {
            ProcessCurrentSheetFormulas();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                // 1. 获取桌面路径
                string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);

                // 2. 生成文件名 (格式: CIP listYYYYMMDD.xlsx)
                string fileName = $"CIP list{DateTime.Now:yyyyMMdd}.xlsx";

                // 3. 组合完整的文件路径
                string fullPath = Path.Combine(desktopPath, fileName);

                // 4. 导出文档为 XLSX 格式
                // 重要提示：确保 spreadsheetCIPList 是您 SpreadsheetControl 控件的正确名称
                spreadsheetCIPList.SaveDocument(fullPath, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                // 5. 显示成功消息
                MessageBox.Show($"File successfully exported to desktop:\n{fullPath}", "Export Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (IOException ioEx)
            {
                // 处理文件占用等IO错误
                MessageBox.Show($"Error exporting file: The file may be in use by another program.\n{ioEx.Message}", "Export Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            catch (Exception ex)
            {
                // 处理其他未知错误
                MessageBox.Show($"Unknown error exporting file: {ex.Message}", "Export Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (isDirty)

            {
                DialogResult result = MessageBox.Show("Do you want to save changes?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    // 用户点击 "是"，调用 ButtonSave_Click 方法
                    btnSave_Click(null, EventArgs.Empty);
                    this.Close();
                }
                else if (result == DialogResult.No)
                {
                    // 用户选择 "否"
                    MessageBox.Show("Not saved!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    this.Close();
                }

            }
            else
            {
                this.Close();
            }


        }

        private void CreatePivotTable()
        {
            try
            {
                // 获取第3个工作表（索引为2）作为数据源
                Worksheet overviewSheet = spreadsheetCIPList.Document.Worksheets["Overview"];
                // 获取第2个工作表（索引为1）作为目标位置
                Worksheet PivotTableSheet = spreadsheetCIPList.Document.Worksheets["Pivot table"];
                //表格Sheet
                Worksheet chartSheet = spreadsheetCIPList.Document.Worksheets["Summary"];

                // 定义数据源范围 A3:CL500
                CellRange sourceRange = overviewSheet.Range["A3:CK500"];

                // 定义目标位置（从A1开始）
                CellRange targetRange = PivotTableSheet.Range["A1"];

                // 创建数据透视表
                PivotTable pivotTable = PivotTableSheet.PivotTables.Add(sourceRange, targetRange, "CIP_PivotTable");


                // 设置行字段（第6列 F -> 索引为5）Department
                PivotField rowField = pivotTable.Fields[5];
                pivotTable.RowFields.Add(rowField);
                //按部门排序
                rowField.SortType = PivotFieldSortType.Ascending;


                // 添加值字段（第11列 K -> 索引10），汇总方式为Sum，今年预计
                PivotField valueField1 = pivotTable.Fields[10];
                PivotDataField dataField1 = pivotTable.DataFields.Add(valueField1);
                dataField1.SummarizeValuesBy = PivotDataConsolidationFunction.Sum;
                dataField1.Name = "Planned saving";

                // 添加值字段（第51列 AY -> 索引50），汇总方式为Sum，今年实际
                PivotField valueField2 = pivotTable.Fields[50];
                PivotDataField dataField2 = pivotTable.DataFields.Add(valueField2);
                dataField2.SummarizeValuesBy = PivotDataConsolidationFunction.Sum;
                dataField2.Name = "Actual saving";

                //添加12个月的字段

                PivotField valueFieldPlan;
                PivotDataField dataFieldPlan;
                PivotField valueFieldActual;
                PivotDataField dataFieldActual;

                for (int i = 1; i <= 12; i++)
                {
                    //Plan
                    valueFieldPlan = pivotTable.Fields[i + 24];
                    dataFieldPlan = pivotTable.DataFields.Add(valueFieldPlan);
                    dataFieldPlan.SummarizeValuesBy = PivotDataConsolidationFunction.Sum;
                    //月英文缩写
                    string englishAbbreviation = System.Globalization.CultureInfo.GetCultureInfo("en-US").DateTimeFormat.GetAbbreviatedMonthName(i);
                    dataFieldPlan.Name = $"{englishAbbreviation}" + "-P";

                }

                for (int i = 1; i <= 12; i++)
                {

                    //Actual
                    valueFieldActual = pivotTable.Fields[i + 64];
                    dataFieldActual = pivotTable.DataFields.Add(valueFieldActual);
                    dataFieldActual.SummarizeValuesBy = PivotDataConsolidationFunction.Sum;
                    string englishAbbreviation = System.Globalization.CultureInfo.GetCultureInfo("en-US").DateTimeFormat.GetAbbreviatedMonthName(i);
                    dataFieldActual.Name = $"{englishAbbreviation}" + "-A";

                }





                // 刷新缓存
                pivotTable.Cache.Refresh();

                // 设置样式
                pivotTable.Style = spreadsheetCIPList.Document.TableStyles["PivotStyleMedium2"];
                pivotTable.ShowRowHeaders = true;
                pivotTable.ShowColumnHeaders = true;
                pivotTable.BandedColumns = true;

                // 设置数据透视表的数字格式
                CellRange pivotRange = pivotTable.Location.WholeRange;
                pivotRange.NumberFormat = "#,##0"; // 精确到各位，不要小数，带千分号


                //// ---------- 添加数据透视图 ----------
                //Chart chart = chartSheet.Charts.Add(ChartType.ColumnClustered);

                //// 设置图表数据来源：使用数据透视表的 WholeRange，并交换横纵坐标
                //CellRange wholeRange = pivotTable.Location.WholeRange;

                //// 计算范围：从 WholeRange 的左上角，截取到倒数第2行（排除 Grand Total）
                //int startRow = wholeRange.TopRowIndex;
                //int startCol = wholeRange.LeftColumnIndex;
                //int endRow = wholeRange.BottomRowIndex - 1; // 向上截去1行
                ////不纳入分月数据
                //int endCol = wholeRange.LeftColumnIndex + 2;

                //CellRange chartRange = wholeRange.Worksheet.Range.FromLTRB(startCol, startRow, endCol, endRow);
                //chart.SelectData(chartRange, ChartDataDirection.Column);

                //// 设置图表位置（例如 H20 到 P35）
                //chart.TopLeftCell = chartSheet.Cells["C3"];
                //chart.BottomRightCell = chartSheet.Cells["M18"];

                //// 设置图表标题
                //chart.Title.Visible = true;
                //chart.Title.SetValue("CIP 节省汇总图表");


                //// 数据标签
                //if (chart.Views.Count > 0)
                //{
                //    var chartView = chart.Views[0];

                //    // 启用数据标签
                //    chartView.DataLabels.ShowValue = true;
                //    chartView.DataLabels.LabelPosition = DevExpress.Spreadsheet.Charts.DataLabelPosition.OutsideEnd;

                //    // 设置数字格式
                //    chartView.DataLabels.NumberFormat.FormatCode = "#,##0";
                //}
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Creating pivot table: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        private void CreatDataSummary()
        {

            int lastmonth = CIPSFMView.selectdate.Month - 1;
            //查出透视表会有多少个不同的部门数量
            int depcounts =0 ;

            // 创建一个 DateTime 变量，值为去年 1 月 1 日
            DateTime currentDate = CIPSFMView.selectdate;
            int lastYear = currentDate.Year - 1;
            DateTime lastYearJanuaryFirst = new DateTime(lastYear, 1, 1);
            //查询逻辑：A.工厂所有CIP L2、3   B.非ILX    C.上一年+今年所有项目
            //先获取所有符合项目的清单,工厂所有
            DataTable dt = LSQL.SQL.ReturnDataTableDistinc(SystemInfo.SQL, "DepartmentName", "View_CIP_list",  $"delTag=0 and (IL != 'ILX' OR IL IS NULL)  and createDate >='{lastYearJanuaryFirst}' ");
            depcounts = dt.Rows.Count;



            depcounts = dt.Rows.Count;

            Worksheet PivotTableSheet = spreadsheetCIPList.Document.Worksheets["Pivot table"];

            PivotTableSheet.Cells[1, 27].Value = "Department";
            PivotTableSheet.Cells[1, 28].Value = "Target";
            PivotTableSheet.Cells[1, 29].Value = "YTD Planned+Actual saving";
            PivotTableSheet.Cells[1, 30].Value = "YTD Planned+Actual saving/Target";
            PivotTableSheet.Cells[1, 31].Value = "Planned saving";
            PivotTableSheet.Cells[1, 32].Value = "YTD planned saving";
            PivotTableSheet.Cells[1, 33].Value = "Actual saving";
            PivotTableSheet.Cells[1, 34].Value = "Actual saving/YTD planned saving";
            PivotTableSheet.Cells[1, 35].Value = "Actual saving/Target";
            ////查询是哪个工厂的数据库，为循环时写入对应工厂的部门清单用
            //string connectionString = SystemInfo.SQL;

            //// 从连接字符串中提取数据库名称,透视表和Overview都需要
            //string databaseName = "";
            //string[] connectionParts = connectionString.Split(';');
            //foreach (string part in connectionParts)
            //{
            //    if (part.Trim().StartsWith("Database=", StringComparison.OrdinalIgnoreCase))
            //    {
            //        databaseName = part.Trim().Substring(9);
            //        break;
            //    }
            //}
            ////透视表写入部门列表
            //if (databaseName == "SFM_MECYPiston")
            //{
            //    PivotTableSheet.Cells[27, 2].Value = "01Foundry";
            //    PivotTableSheet.Cells[27, 3].Value = "02Machining";
            //    PivotTableSheet.Cells[27, 4].Value = "03FI/ST/PA";
            //    PivotTableSheet.Cells[27, 5].Value = "04M/c Building";
            //    PivotTableSheet.Cells[27, 6].Value = "05Logistics";
            //    PivotTableSheet.Cells[27, 7].Value = "06Quality";
            //    PivotTableSheet.Cells[27, 8].Value = "07PS/PT";
            //    PivotTableSheet.Cells[27, 9].Value = "08Others";



            //}
            //else if (databaseName == "SFM_MECYBearing")
            //{
            //    PivotTableSheet.Cells[27, 2].Value = "01Production_Maching";
            //    PivotTableSheet.Cells[27, 3].Value = "02Production_Polymer";
            //    PivotTableSheet.Cells[27, 4].Value = "03Production_Bushing";
            //    PivotTableSheet.Cells[27, 5].Value = "04PT";
            //    PivotTableSheet.Cells[27, 6].Value = "05Quality";
            //    PivotTableSheet.Cells[27, 7].Value = "06Logistics";
            //    PivotTableSheet.Cells[27, 8].Value = "07Maintenance";
            //    PivotTableSheet.Cells[27, 9].Value = "08Tooling";
            //    PivotTableSheet.Cells[27, 10].Value = "09Others";


            //}

            ////Target,测试用的数据
            //PivotTableSheet.Cells[2, 28].Value = 1000;//铸造
            //PivotTableSheet.Cells[3, 28].Value = 1000;//加工

            //PivotTableSheet.Cells[4, 28].Value = 1000;//成检

            //PivotTableSheet.Cells[5, 28].Value = 1000;//模具

            //PivotTableSheet.Cells[6, 28].Value = 1000;//物流

            //PivotTableSheet.Cells[7, 28].Value = 1000;//质量

            //PivotTableSheet.Cells[8, 28].Value = 1000;//生产技术

            //PivotTableSheet.Cells[9, 28].Value = 1000;//生产技术

            //把月份信息写上，便于分别
            PivotTableSheet.Cells[0, 42].Value = CIPSFMView.selectdate.AddMonths(-1);
            PivotTableSheet.Cells[0, 46].Value = CIPSFMView.selectdate;






            for (int i = 1; i <= depcounts; i++)
            {
                float ytdPlan = 0;
                float ytdActual = 0;
                float ytdPlanPlusActual = 0;
                float target = 0;

                //department
                string department = PivotTableSheet.Cells[1 + i, 0].Value.ToString();
                PivotTableSheet.Cells[1 + i, 27].Value = PivotTableSheet.Cells[1 + i, 0].Value;
                // 使用?.ToString() ?? ""简化空值检查
                string targetValueStr = PivotTableSheet.Cells[1 + i, 28].Value?.ToString() ?? "";
                if (!string.IsNullOrEmpty(targetValueStr.Trim()))
                {
                    float targetValue;
                    if (float.TryParse(targetValueStr, out targetValue))
                    {
                        target = targetValue;
                    }
                }
                //ytd plan ,ytd actual
                for (int j = 1; j <= lastmonth; j++)
                {
                    // 使用?.ToString() ?? ""简化空值检查
                    string planValueStr = PivotTableSheet.Cells[1 + i, 2 + j].Value?.ToString() ?? "";
                    if (!string.IsNullOrEmpty(planValueStr.Trim()))
                    {
                        float planValue;
                        if (float.TryParse(planValueStr, out planValue))
                        {
                            ytdPlan += planValue;
                        }
                    }

                    string actualValueStr = PivotTableSheet.Cells[1 + i, 14 + j].Value?.ToString() ?? "";
                    if (!string.IsNullOrEmpty(actualValueStr.Trim()))
                    {
                        float actualValue;
                        if (float.TryParse(actualValueStr, out actualValue))
                        {
                            ytdActual += actualValue;
                        }
                    }
                }
                PivotTableSheet.Cells[1 + i, 32].Value = ytdPlan;
                PivotTableSheet.Cells[1 + i, 33].Value = ytdActual;

                //ytd plan + actual
                ytdPlanPlusActual = ytdActual;
                for (int j = lastmonth + 1; j <= 12; j++)
                {
                    // 使用?.ToString() ?? ""简化空值检查
                    string planValueStr = PivotTableSheet.Cells[1 + i, 2 + j].Value?.ToString() ?? "";
                    if (!string.IsNullOrEmpty(planValueStr.Trim()))
                    {
                        float planValue;
                        if (float.TryParse(planValueStr, out planValue))
                        {
                            ytdPlanPlusActual += planValue;
                        }
                    }
                }
                PivotTableSheet.Cells[1 + i, 29].Value = ytdPlanPlusActual;
                //YTD Planned+Actual saving/Target
                if (target > 0)
                {
                    PivotTableSheet.Cells[1 + i, 30].Value = ytdPlanPlusActual / target;
                    // 转化为百分数
                    PivotTableSheet.Cells[1 + i, 30].NumberFormat = "0%";

                }

                //Planned saving
                PivotTableSheet.Cells[1 + i, 31].Value = PivotTableSheet.Cells[1 + i, 1].Value;

                //Actual saving/YTD planned saving

                if (ytdPlan > 0)
                {
                    PivotTableSheet.Cells[1 + i, 34].Value = ytdActual / ytdPlan;
                    // 转化为百分数
                    PivotTableSheet.Cells[1 + i, 34].NumberFormat = "0%";

                }

                //Actual saving/Target
                if (target > 0)
                {
                    PivotTableSheet.Cells[1 + i, 35].Value = ytdActual / target;
                    // 转化为百分数
                    PivotTableSheet.Cells[1 + i, 35].NumberFormat = "0%";

                }

                //生成项目状态数据,方法中传入的是部门，且去掉数字编号，软编码后禁用了
                //int[] status = CIPSFMView.CalculateStatus(department.Substring(2));
                int[] status = CIPSFMView.CalculateStatus(department);
                PivotTableSheet.Cells[1 + i, 36].Value = status[0];//completed
                PivotTableSheet.Cells[1 + i, 37].Value = status[1];//active
                PivotTableSheet.Cells[1 + i, 38].Value = status[2];//overdue
                PivotTableSheet.Cells[1 + i, 39].Value = status[0] + status[1] + status[2];//total
                //overdue rate
                if (status[0] + status[1] + status[2] > 0)
                {
                    //乘1.0是为了防止得数默认取整
                    PivotTableSheet.Cells[1 + i, 40].Value = 1.00 * status[2] / (status[0] + status[1] + status[2]);

                    PivotTableSheet.Cells[1 + i, 40].NumberFormat = "0%";

                }
                ////生成IL数量，方法中传入的是部门，且去掉数字编号
                //int[] IL = CalculateIL(department.Substring(2));
                int[] IL = CalculateIL(department);

                for (int j = 0; j < IL.Length; j++)
                {
                    PivotTableSheet.Cells[1 + i, 41 + j].Value = IL[j];
                }






                // 自动列宽
                PivotTableSheet.Range["A1:AZ100"].AutoFitColumns();
            }

        }


        private int[] CalculateIL(string department)
        {

            //上个月
            DataTable dtlastmonth = CIPSFMView.GetLatestILDataByDepartment(-1, false, CIPSFMView.selectdate);
            DataTable dtthismonth = CIPSFMView.GetLatestILDataByDepartment(0, false, CIPSFMView.selectdate);
            int il1 = 0; int il2 = 0; int il3 = 0; int il4 = 0; int il5 = 0; int il6 = 0; int total = 0; int il123 = 0;
            int IL1 = 0; int IL2 = 0; int IL3 = 0; int IL4 = 0; int IL5 = 0; int IL6 = 0; int TOTAL = 0; int IL123 = 0;
            int add1 = 0; int add2 = 0; int add3 = 0; int add4 = 0; int add5 = 0; int add6 = 0; int addTotal = 0; int add123 = 0;


            if (dtlastmonth.Rows.Count > 0)
            {
                DataRow[] matchingRows = dtlastmonth.Select($"department = '{department}'");
                if (matchingRows.Length > 0)
                {
                    DataRow dataRow = matchingRows[0];
                    il1 += Convert.ToInt32(dataRow["IL1"]);
                    il2 += Convert.ToInt32(dataRow["IL2"]);
                    il3 += Convert.ToInt32(dataRow["IL3"]);
                    il4 += Convert.ToInt32(dataRow["IL4"]);
                    il5 += Convert.ToInt32(dataRow["IL5"]);
                    il6 += Convert.ToInt32(dataRow["IL6"]);
                    il123 += Convert.ToInt32(dataRow["IL123"]);
                    total += Convert.ToInt32(dataRow["total"]);

                }

            }
            if (dtthismonth.Rows.Count > 0)
            {
                DataRow[] matchingRows = dtthismonth.Select($"department = '{department}'");
                if (matchingRows.Length > 0)
                {
                    DataRow dataRow = matchingRows[0];
                    IL1 += Convert.ToInt32(dataRow["IL1"]);
                    IL2 += Convert.ToInt32(dataRow["IL2"]);
                    IL3 += Convert.ToInt32(dataRow["IL3"]);
                    IL4 += Convert.ToInt32(dataRow["IL4"]);
                    IL5 += Convert.ToInt32(dataRow["IL5"]);
                    IL6 += Convert.ToInt32(dataRow["IL6"]);
                    IL123 += Convert.ToInt32(dataRow["IL123"]);
                    TOTAL += Convert.ToInt32(dataRow["total"]);

                }

            }
            add1 = IL1 - il1;
            add2 = IL2 - il2;
            add3 = IL3 - il3;
            add4 = IL4 - il4;
            add5 = IL5 - il5;
            add6 = IL6 - il6;
            add123 = IL123 - il123;
            addTotal = TOTAL - total;
            return new int[] { il123, il4, il5, il6, IL123, IL4, IL5, IL6, add123, add4, add5, add6 };

        }

        private void CreateChart()
        {
            Worksheet worksheet = spreadsheetCIPList.Document.Worksheets["Pivot table"];
            Worksheet chartsheet = spreadsheetCIPList.Document.Worksheets["Summary"];


            // 添加柱形图
            Chart chart = chartsheet.Charts.Add(ChartType.ColumnClustered);

            // 设置图表数据来源（包含系列名称和数据）
            CellRange chartRange = worksheet.Range["AB2:AE5"];
            chart.SelectData(chartRange, ChartDataDirection.Column);

            // 设置图表位置和大小
            chart.TopLeftCell = worksheet.Cells["M1"];
            chart.BottomRightCell = worksheet.Cells["W15"];

            // 设置图表标题
            chart.Title.Visible = true;
            chart.Title.SetValue("Saving Completed Rate");

            // 全局启用数据标签
            if (chart.Views.Count > 0)
            {
                var chartView = chart.Views[0];

                // 启用数据标签
                chartView.DataLabels.ShowValue = true;
                chartView.DataLabels.LabelPosition = DevExpress.Spreadsheet.Charts.DataLabelPosition.OutsideEnd;

                // 设置数字格式
                chartView.DataLabels.NumberFormat.FormatCode = "#,##0";
            }



            // 然后设置第三个系列为折线图并使用次坐标轴
            chart.Series[2].ChangeType(ChartType.Line);

            // 使用AxisGroup来设置次坐标轴
            chart.Series[2].AxisGroup = DevExpress.Spreadsheet.Charts.AxisGroup.Secondary;
            //}

        }

        private void CostSavingLoader_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (isDirty)

            {
                DialogResult result = MessageBox.Show("Do you want to save changes?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    // 用户点击 "是"，调用 ButtonSave_Click 方法
                    btnSave_Click(null, EventArgs.Empty);
                }
                else if (result == DialogResult.No)
                {
                    // 用户选择 "否"
                    MessageBox.Show("Not saved!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }
                else
                {
                    e.Cancel = true;
                }
            }
        }

        private void btnLoadSheetData_Click(object sender, EventArgs e)
        {
            try
            {
                int projectPID = CIPAdd.SelectPID; // 或者你想要的PID
                                                   // 查询SheetData
                DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_list", $"PID={projectPID} and delTag=0");
                if (dt.Rows.Count == 0)
                {
                    MessageBox.Show($"Project with PID {projectPID} not found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                if (dt.Rows[0]["SheetData"] == DBNull.Value)
                {
                    MessageBox.Show("This project has no SheetData", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                byte[] sheetBytes = (byte[])dt.Rows[0]["SheetData"];
                using (MemoryStream ms = new MemoryStream(sheetBytes))
                {
                    spreadsheetCIPList.LoadDocument(ms, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                }
                MessageBox.Show("SheetData loaded successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to load SheetData: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void LoadSheetData_Byte(int projectPID)
        {
            try
            {
                if (isOverviewwithSheets == false)
                {
                    projectPID = CIPAdd.SelectPID;
                }

                // 查询SheetData
                DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_list", $"PID={projectPID} and delTag=0");
                if (dt.Rows.Count == 0)
                {
                    return;
                }

                if (dt.Rows[0]["SheetData"] == DBNull.Value)
                {
                    return;
                }

                byte[] sheetBytes = (byte[])dt.Rows[0]["SheetData"];

                // 创建临时SpreadsheetControl作为中间载体（避免直接new Workbook）
                using (var tempSpreadsheet = new DevExpress.XtraSpreadsheet.SpreadsheetControl())
                {
                    // 加载字节数组到临时控件的Document中
                    using (MemoryStream ms = new MemoryStream(sheetBytes))
                    {
                        tempSpreadsheet.Document.LoadDocument(ms, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                    }

                    // 确认临时文档中至少有一个工作表
                    if (tempSpreadsheet.Document.Worksheets.Count == 0)
                    {
                        return;
                    }

                    // 获取临时文档中的唯一工作表
                    Worksheet tempSheet = tempSpreadsheet.Document.Worksheets[0];

                    // 生成唯一名称（使用原名称，重复则加后缀）
                    string newSheetName = GetUniqueSheetName(tempSheet.Name);

                    // 在当前工作簿中添加新工作表，并复制临时工作表内容
                    Worksheet newSheet = spreadsheetCIPList.Document.Worksheets.Add(newSheetName);
                    newSheet.CopyFrom(tempSheet);

                    // 设置新增工作表为活动表
                    spreadsheetCIPList.Document.Worksheets.ActiveWorksheet = newSheet;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to load and add Sheet: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        // 辅助方法：生成唯一工作表名称（复用之前的逻辑）
        private string GetUniqueSheetName(string baseName)
        {
            string newName = baseName;
            int suffix = 1;
            while (spreadsheetCIPList.Document.Worksheets.Contains(newName))
            {
                newName = $"{baseName}_{suffix}";
                suffix++;
            }
            return newName;
        }

        private void spreadsheetCIPList_PopupMenuShowing(object sender, DevExpress.XtraSpreadsheet.PopupMenuShowingEventArgs e)
        {
            if (e.MenuType == DevExpress.XtraSpreadsheet.SpreadsheetMenuType.RowHeading)
            {
                // Remove the "Clear Contents" menu item.
                e.Menu.DisableMenuItem(SpreadsheetCommandId.InsertSheetRowsContextMenuItem);
                e.Menu.DisableMenuItem(SpreadsheetCommandId.RemoveSheetRowsContextMenuItem);

            }
            if (e.MenuType == DevExpress.XtraSpreadsheet.SpreadsheetMenuType.ColumnHeading)
            {
                // Remove the "Clear Contents" menu item.
                e.Menu.DisableMenuItem(SpreadsheetCommandId.InsertSheetColumnsContextMenuItem);
                e.Menu.DisableMenuItem(SpreadsheetCommandId.RemoveSheetColumnsContextMenuItem);

            }

        }
    }
}